package klex.eserver.common;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import klex.eserver.dao.BaseDao;
import oracle.jdbc.rowset.OracleCachedRowSet;

import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.Transaction;
import org.springframework.orm.hibernate3.support.HibernateDaoSupport;


/**
 * @author kevin
 * 该工具类用分页处理，传入sql，自动计算分页生成分页sql
 * 注意：请勿使用（+）进行左右关联，jdbc预处理不会识别
 */
public class PageHelper extends HibernateDaoSupport{
	SessionFactory sessionFactory ;
	Connection con = null;
	ResultSet res = null;
	PreparedStatement pstmt = null;

	/**
	 * 返回计算好sql
	 * @param sql 要分页的sql
	 * @param pageNum 第几页
	 * @param perPageCount 每页多少条
	 * @return map key: pageIndex 下标，pageTaltal 总记录数,pageSql 分页的sql
	 */
	public Map getPaqeInfo(String sql,int pageNum,int perPageCount){
		int total = 0;//总条数
		Map pgInfoMap = null;
		try{
			//OracleCachedRowSet ocrs = new OracleCachedRowSet();  
			con = BaseDao.getConnection();
			//pstmt  =  con.prepareStatement("select count(*) as kv_one from ("+sql+")");
			Statement stm= con.createStatement();
			String sqltemp = "select sum(1) as kvone from ("+sql+")";
			res = stm.executeQuery(sqltemp);
			//ocrs.populate(res);
			//ocrs.last();
			//计算下标
			res.next();
			total= res.getInt("kvone");//ocrs.getRow();//总共有多少条
		    int a = total/perPageCount;//求商
		    int b = total%perPageCount; //求余
		    if(b>0)a=a+1;
		    pgInfoMap = new HashMap();//存放sql和下标的map
		    pgInfoMap.put("pageIndex",a);//把计算好的下标放map
		    pgInfoMap.put("pageTaltal",total);//总记录数
		    String fsql = getsql(total, a,b, sql,pageNum,perPageCount);//计算rownum,拼sql
		    pgInfoMap.put("pageSql",fsql);//分页的sql
		}catch (Exception e) {
			System.out.println(e);
		} finally {
			BaseDao.closeConnection(con, pstmt, res);
		}
		return pgInfoMap;
	} 
	/**
	 * 使用hibernate进行分页处理（用于含左关联，子查询的语句）
	 * @return
	 */
	public  Map getPaqeInfoHb(String sql,int pageNum,int perPageCount){
		String sqltemp = "select sum(1) as kvone,1 from ("+sql+")";
		Session session = this.getHibernateTemplate().getSessionFactory().getCurrentSession(); 
        //Transaction t = session.beginTransaction();
        List list =  session.createSQLQuery(sqltemp).list();
        Object obj[] = (Object[])list.get(0);
       // t.commit();
        //session.flush();
//        if(session!=null){
//            session.close();
//            session=null;
//        }
        //this.releaseSession(session);
        String t2 =  obj[0]+"";
        //if(t==null || "null".equals(t2))t2="0";
        if("null".equals(t2))t2="0";
    	int total= Integer.parseInt(t2);
	    int a = total/perPageCount;//求商
	    int b = total%perPageCount; //求余
	    if(b>0)a=a+1;        
        Map pgInfoMap = new HashMap();//存放sql和下标的map
	    pgInfoMap.put("pageIndex",a);//把计算好的下标放map
	    pgInfoMap.put("pageTaltal",total);//总记录数
	    String fsql = getsql(total, a,b, sql,pageNum,perPageCount);//计算rownum,拼sql
	    pgInfoMap.put("pageSql",fsql);//分页的sql
        return pgInfoMap;
	}
	/**
	 * 计算起始的rownum,组织好sql
	 * @return
	 */
	//总记录，下标个数，最后一页的条数，sql,pageNum用户选择第几页,perPageCount每页多少条
	private String getsql(int total,int a,int b,String sql,int pageNum,int perPageCount){
		int s =0;//起始rownum
		int e =0;//结束rownum
		if(pageNum==1){//第一页
			s =1;
			e = perPageCount;
		}else if(pageNum>1 && pageNum<a){//中间页
			s = perPageCount*pageNum - perPageCount +1;
			e = perPageCount*pageNum;
		}else if(pageNum == a){//最后一页
			if (b == 0) {
				b = perPageCount;
			}
			s = total - b +1;
			e = total;
		}
	    String tempSql = " select * from ( SELECT kv_a.*,ROWNUM AS kv_rn  FROM ( "+sql+")kv_a ) where kv_rn >="+s+" and kv_rn <="+e;
		return tempSql;
	}
}
